# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
import openpyxl as openpyxl
import pymysql as pymysql
# useful for handling different item types with a single interface
from itemadapter import ItemAdapter


class MysqlPipline:
    def __init__(self):
        self.connect = pymysql.connect(host='8.137.150.77', port=3303,
                                       user='root', password='123456',
                                       database='scrapy', charset='utf8mb4')
        self.cursor = self.connect.cursor()
        self.data = []
        self.item = None

    def open_spider(self, spider):
        print('MysqlPipline:', '开始抓取。。。。！')


    def close_spider(self, spider):
        if len(self.data) > 0:
            self._write_mysql_()
        self.connect.close()


    def process_item(self, item, spider):
        title = item.get('title', '')
        price = item.get('price', '')
        deal_count = item.get('deal_count', '')
        shop_name = item.get('shop_name', '')
        location = item.get('location', '')
        if len(title) == 2:
            name = title[1]
            self.data.append((shop_name, name, price, deal_count, location))
            if len(self.data) == 48:
                self._write_mysql_()
                self.data.clear()
            return item
        name = title[0]
        self.data.append((shop_name, name, price, deal_count, location))
        if len(self.data) == 48:
            self._write_mysql_()
            self.data.clear()
        return item


    def _write_mysql_(self):
        for self.item in self.data:
            sql = 'select * from taobao where shop_name = %s'

            check_data = self.cursor.execute(
                sql, self.item[0]
            )
            if check_data == 0:
                print('data:', self.item)
                self.cursor.execute(
                    'insert into taobao (shop_name, title, price, deal_count, location) values (%s, %s, %s, %s, %s)',
                    self.item
                )
                self.connect.commit()
            continue


class ExcelPipeline:
    # 创建excel表
    def __init__(self):
        self.name = None
        self.wb = openpyxl.Workbook()
        self.ws = self.wb.active
        self.ws.title = 'Top250'
        self.ws.append(('标题', '价格', '购买信息', '商铺', '地区'))

    def open_spider(self, spider):
        print('ExcelPipeline:', '开始抓取。。。。！')


    def close_spider(self, spider):
        self.wb.save('淘宝.xlsx')

    # 拿到数据，提取对应的值，存入表中
    def process_item(self, item, spider):
        title = item.get('title', '')
        price = item.get('price', 0)
        deal_count = item.get('deal_count', '')
        shop_name = item.get('shop_name', '')
        location = item.get('location', '')

        if len(title) == 2:
            name = title[1]
            self.ws.append((name, price, deal_count, shop_name, location))
            return item
        name = title[0]
        self.ws.append((name, price, deal_count, shop_name, location))
        return item
